package com.easyedu.core.dao;

import com.easyedu.core.entity.dto.OpenCourseInfoByVersion;
import com.easyedu.core.entity.dto.TimeTableIndex;
import com.easyedu.core.entity.po.HasArrangeCourse;
import com.easyedu.core.entity.po.OpenCourse;
import com.easyedu.core.entity.po.OpenCourseTime;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * @Author lmr
 **/
@Mapper
public interface OpenCourseDao {

     /**
      * 功能描述:通过id删除已开课程
      *
      * @param openCourseId 1
      * @return : java.lang.Integer
      **/
     @Delete("delete from open_course where open_courses_id = #{openCourseId}")
     Integer deleteById(Long openCourseId);

     /**
      * 功能描述:获取开课信息
      *
      * @param courseNumber 1
      * @return : com.easyedu.api.entity.DO.OpenCourse
      **/
     @Select("select * from open_course where course_number = #{courseNumber}")
     OpenCourse getByCourseNumber(String courseNumber);

     /**
      * 功能描述:创建一个新的课程
      *
      * @param openCourse 1
      * @return : java.lang.Integer
      **/
     @Insert("insert into open_course(cultivation_scheme_id,open_year, school_year, semester, open_institute_id, "
             + "open_institute_name, " + "teach_institute_id,"
             + "                         teach_institute_name, open_department_id, open_department_name, course_code,"
             + " course_number,"
             + "                         course_name, teacher_id, teacher_name, credit, theory_class_hour, "
             + "experiment_class_hour,"
             + "                         total_class_hour, usual_score_ratio, experiment_score_ratio, "
             + "exam_score_ratio, capacity,"
             + "                         free_capacity, is_open, is_repeat, create_time, update_time)"
             + "VALUES (#{cultivationSchemeId},#{openYear},#{schoolYear},#{semester},#{openInstituteId},"
             + "#{openInstituteName},"
             + "#{teachInstituteId},#{teachInstituteName},#{openDepartmentId},#{openDepartmentName},#{courseCode},"
             + "#{courseNumber},#{courseName},#{teacherId},#{teacherName},#{credit},#{theoryClassHour},"
             + "#{experimentClassHour},#{totalClassHour},#{usualScoreRatio},#{experimentScoreRatio},#{examScoreRatio},"
             + "#{capacity},#{freeCapacity},#{isOpen},#{isRepeat},NOW(),NOW())")
     Integer createOpenCourse(OpenCourse openCourse);

     /**
      * 功能描述:设置课程为可选
      *
      * @param ids 1
      * @return : java.lang.Integer
      **/
     Integer updateCourseSetOpen(List<Long> ids);

     /**
      * 功能描述:设置课程为不可选
      *
      * @param ids 1
      * @return : java.lang.Integer
      **/
     Integer updateCourseSetClose(List<Long> ids);

     /**
      * 功能描述:获取开课
      *
      * @param openCourse 1
      * @return : com.easyedu.api.entity.DO.OpenCourse
      **/
     List<OpenCourse> getOpenCourse(OpenCourse openCourse);

     /**
      * 功能描述:根据课程id获取课程信息
      *
      * @param ids 1
      * @return : java.util.List<com.easyedu.api.entity.DO.OpenCourse>
      **/
     List<OpenCourse> getOpenCourseByIds(List<Long> ids);

     /**
      * 功能描述: 获取已开放课程
      *
      * @return : java.util.List<com.easyedu.api.entity.DO.OpenCourse>
      **/
     @Select("select * from open_course where is_open = 2")
     List<OpenCourse> getHasOpenCourse();

     /**
      * 功能描述:根据课程id获取已开课程信息
      *
      * @param openCourseId 1
      * @return : com.easyedu.api.entity.DO.OpenCourse
      **/
     @Select("select * from open_course where open_courses_id = #{openCourseId}")
     OpenCourse getOpenCourseById(Long openCourseId);

     @Select("select open_courses_id,open_week,open_lesson,open_day from open_course_time where open_year = #{year} "
             + "and semester = #{semester} group by open_courses_id, open_week, open_lesson, open_day ")
     List<OpenCourseTime> getOpenCourseTime(Integer year, String semester);

     @Select("select classroom from open_course_time where open_year = #{year} and semester = #{semester} group by "
             + "classroom")
     List<String> getOpenCourseClassRoom(Integer year, String semester);

     @Select("select o.course_number, o.course_name, oc.teacher_name, o.open_week, o.open_day, o.open_lesson,"
             + "b.building_id, b.build_name, c.classroom_id, c.classroom_name, c.floor "
             + "from open_course_time o, building_address b, classroom c, open_course oc " + "where "
             + "b.build_name = c.belong_build and o.classroom = c.classroom_name and "
             + "oc.open_courses_id = o.open_courses_id and " + "o.open_year = #{openYear} and "
             + "o.semester = #{semester} and " + "o.open_week= #{week} and " + "b.build_name = #{building} and "
             + "o.classroom = #{classroom}")
     List<HasArrangeCourse> getHasArrangeCourse(int openYear, String semester, String building, int week,
             String classroom);

     /**
      * 获取未排课程
      *
      * @param departmentId
      * @param openYear
      * @param schoolYear
      * @param semester
      * @param versionId
      * @return
      */
     @Select("select * from open_course where open_year = #{openYear} and school_year = #{schoolYear} and semester = "
             + "#{semester} and open_department_id = #{departmentId} and open_courses_id not in "
             + "(select  open_courses_id from open_course_time_mid where open_course_time_mid_version_id = "
             + "#{versionId})")
     List<OpenCourse> getNotArrangeCourse(long departmentId, int openYear, String schoolYear, String semester,
             String versionId);

     /**
      * 获取未排课程
      *
      * @param openYear
      * @param semester
      * @param versionId
      * @return
      */
     @Select("select * from open_course where open_year = #{openYear}  and semester = "
             + "#{semester}  and open_courses_id not in " + "(select  open_courses_id from open_course_time_mid where "
             + "open_course_time_mid_version_id = #{versionId})")
     List<OpenCourse> getNotArrangeCourseByStatus(int openYear, String semester, String versionId);

     /**
      * 获取已排课程
      *
      * @param openYear
      * @param semester
      * @return
      */
     @Select("select * from open_course where open_year = #{openYear}  and semester = "
             + "#{semester}  and open_courses_id in " + "(select  open_courses_id from open_course_time_mid where "
             + "open_course_time_mid_version_id = #{versionId})")
     List<OpenCourse> getHasArrangeCourseByStatus(int openYear, String semester, String versionId);

     @Update("update open_course set is_arrange = #{status} where open_courses_id = #{openCourseId}")
     Integer setArrangeCourseStatus(long openCourseId, int status);

     /**
      * 根据版本查询专业的课程
      *
      * @param openYear     开课年
      * @param schoolYear   学年
      * @param semester     学期
      * @param instituteId  学院id
      * @param departmentId 专业id
      * @param versionId    版本id
      * @return 已开课程信息
      */
     @Select("select open_day, open_lesson, classroom from open_course_time_mid "
             + "where open_course_time_mid_version_id = #{versionId}  and open_courses_id in ( "
             + "select open_courses_id from open_course where open_year = #{openYear} and school_year = #{schoolYear} "
             + "and semester = #{semester} and open_institute_id = #{instituteId} and open_department_id = "
             + "#{departmentId} ) group by " + "open_day, open_lesson, classroom")
     List<OpenCourseInfoByVersion> getOpenCourseByGradeAndDepartment(int openYear, String schoolYear, String semester,
             long instituteId, long departmentId, String versionId);

     /**
      * 获取教室及位置信息
      *
      * @param year
      * @param semester
      * @return
      */
     @Select("select belong_build,open_week,open_lesson,open_day,classroom from open_course_time o,classroom c where "
             + "o.open_year = #{year} and o.semester = #{semester} and o.classroom = c.classroom_name group by "
             + "belong_build, open_week, open_lesson, open_day,classroom order by open_week asc, open_day asc")
     List<TimeTableIndex> getOpenCourseTimeAndBuilding(Integer year, String semester);

     @Select("select o.course_number, o.course_name, oc.teacher_name, o.open_week, o.open_day, o.open_lesson,"
             + "b.building_id, b.build_name, c.classroom_id, c.classroom_name, c.floor "
             + "from open_course_time o, building_address b, classroom c, open_course oc " + "where "
             + "b.build_name = c.belong_build and o.classroom = c.classroom_name and "
             + "oc.open_courses_id = o.open_courses_id and " + "o.open_year = #{openYear} and "
             + "o.semester = #{semester} ")
     List<HasArrangeCourse> getTimeTableAndBuild(Integer openYear, String semester);

     /**
      * 删除某一学年的课表
      * @param year 年
      * @param semester 学期
      * @return
      */
     @Select("delete from open_course_time where open_year = #{year} and semester = #{semester} ")
     void deleteOpenCourseTime(Integer year, String semester);
}
